﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;
using Follow.Common;

namespace Follow.DataAccess
{
    public class ClienteDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public ClienteDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<Cliente> findAll()
        {
            IList<Cliente> cliente = new List<Cliente>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from cliente where ativo = 1 order by nome";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Cliente c = new Cliente();
                    c.IDCliente = reader.GetInt32(0);
                    c.Nome = reader.GetString(1);
                    c.CNPJ = reader.GetString(2);
                    c.Cidade = reader.GetString(3);
                    c.Estado = reader.GetString(4);
                    c.Ativo = reader.GetBoolean(5);
                    cliente.Add(c);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return cliente;
        }

        public IList<Cliente> findByName(String nome)
        {
            IList<Cliente> cliente = new List<Cliente>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from cliente where nome like '%" + nome + "%' order by nome";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Cliente c = new Cliente();
                    c.IDCliente = reader.GetInt32(0);
                    c.Nome = reader.GetString(1);
                    c.CNPJ = reader.GetString(2);
                    c.Cidade = reader.GetString(3);
                    c.Estado = reader.GetString(4);
                    c.Ativo = reader.GetBoolean(5);
                    cliente.Add(c);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }

            return cliente;
        }

        public IList<Cliente> findByName(String nome, Boolean inativo)
        {
            IList<Cliente> clientes = new List<Cliente>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from cliente where nome like '%" + nome + "%'";
                if (!inativo)
                {
                    sqlCommand += " and ativo = 1";
                }
                sqlCommand += " order by nome;";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Cliente c = new Cliente();
                    c.IDCliente = reader.GetInt32(0);
                    c.Nome = reader.GetString(1);
                    c.CNPJ = reader.GetString(2);
                    c.Cidade = reader.GetString(3);
                    c.Estado = reader.GetString(4);
                    c.Ativo = reader.GetBoolean(5);
                    clientes.Add(c);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return clientes;
        }

        public void insert(Cliente cliente, Int32 idlogin)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " insert into cliente (nome, cnpj, cidade, estado, ativo) values " +
                                        " (@nome, @cnpj, @cidade, @estado, @ativo) ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("nome", cliente.Nome));
                    cmd.Parameters.Add(new SqlParameter("cnpj", cliente.CNPJ));
                    cmd.Parameters.Add(new SqlParameter("cidade", cliente.Cidade));
                    cmd.Parameters.Add(new SqlParameter("estado", cliente.Estado));
                    cmd.Parameters.Add(new SqlParameter("ativo", cliente.Ativo));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Insert", "Cliente");
            }
        }

        public void update(Cliente cliente, Int32 idlogin)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " update cliente set nome = @nome, cnpj = @cnpj, cidade = @cidade," +
                                 " estado = @estado, ativo = @ativo where idcliente = @idcliente;";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idcliente", cliente.IDCliente));
                    cmd.Parameters.Add(new SqlParameter("nome", cliente.Nome));
                    cmd.Parameters.Add(new SqlParameter("cnpj", cliente.CNPJ));
                    cmd.Parameters.Add(new SqlParameter("cidade", cliente.Cidade));
                    cmd.Parameters.Add(new SqlParameter("estado", cliente.Estado));
                    cmd.Parameters.Add(new SqlParameter("ativo", cliente.Ativo));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Update", "Cliente");
            }
        }
        
        public Cliente findById(int id)
        {
            Cliente c = new Cliente();
            try
            {
                conn.Open();
                String sqlCommand = " select * from cliente where idcliente = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    c.IDCliente = reader.GetInt32(0);
                    c.Nome = reader.GetString(1);
                    c.CNPJ = reader.GetString(2);
                    c.Cidade = reader.GetString(3);
                    c.Estado = reader.GetString(4);
                    c.Ativo = reader.GetBoolean(5);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return c;
        }

         public void delete(Int32 id, Int32 idlogin)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " delete from cliente where idcliente = @idcliente ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idcliente", id));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                LogDAO log = new LogDAO();
                log.insert(idlogin, SQLStringSingleton.GetCommandText(cmd), "Delete", "Cliente");
            }
        }
        public IList<Cliente> findByCidade(String cidade)
         {
             IList<Cliente> cliente = new List<Cliente>();
             try
             {
                 conn.Open();
                 String sqlCommand = " select * from cliente where cidade = " + cidade;
                 cmd = new SqlCommand(sqlCommand, conn);
                 reader = cmd.ExecuteReader();

                 while (reader.Read())
                 {
                     Cliente c = new Cliente();
                     c.IDCliente = reader.GetInt32(0);
                     c.Nome = reader.GetString(1);
                     c.Juridico = reader.GetBoolean(2);
                     c.CNPJ = reader.GetString(3);
                     c.Cidade = reader.GetString(4);
                     c.Estado = reader.GetString(5);
                     c.Ativo = reader.GetBoolean(6);
                     cliente.Add(c);
                 }
             }
             finally
             {
                 if (reader != null)
                 {
                     reader.Close();
                 }
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
             return cliente;
         }

         public IList<Cliente> findByAtivo(Boolean ativo)
         {
             IList<Cliente> cliente = new List<Cliente>();
             try
             {
                 conn.Open();
                 String sqlCommand = " select * from cliente where ativo = " + ativo;
                 cmd = new SqlCommand(sqlCommand, conn);
                 reader = cmd.ExecuteReader();

                 while (reader.Read())
                 {
                     Cliente c = new Cliente();
                     c.IDCliente = reader.GetInt32(0);
                     c.Nome = reader.GetString(1);
                     c.Juridico = reader.GetBoolean(2);
                     c.CNPJ = reader.GetString(3);
                     c.Cidade = reader.GetString(4);
                     c.Estado = reader.GetString(5);
                     c.Ativo = reader.GetBoolean(6);
                     cliente.Add(c);
                 }
             }
             finally
             {
                 if (reader != null)
                 {
                     reader.Close();
                 }
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
             return cliente;
         }

         public Cliente findByCnpj(String cnpj)
         {
             Cliente c = new Cliente();
             try
             {
                 conn.Open();
                 String sqlCommand = " select * from cliente where cnpj = @cnpj;";
                 cmd = new SqlCommand(sqlCommand, conn);
                 cmd.Parameters.Add(new SqlParameter("cnpj", cnpj));
                 reader = cmd.ExecuteReader();

                 while (reader.Read())
                 {                    
                     c.IDCliente = reader.GetInt32(0);
                     c.Nome = reader.GetString(1);
                     c.CNPJ = reader.GetString(2);
                     c.Cidade = reader.GetString(3);
                     c.Estado = reader.GetString(4);
                     c.Ativo = reader.GetBoolean(5);
                 }
             }
             finally
             {
                 if (reader != null)
                 {
                     reader.Close();
                 }
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
             return c;
         }

         public IList<Cliente> findByEstado(String estado)
         {
             IList<Cliente> cliente = new List<Cliente>();
             try
             {
                 conn.Open();
                 String sqlCommand = " select * from cliente where estado = " + estado;
                 cmd = new SqlCommand(sqlCommand, conn);
                 reader = cmd.ExecuteReader();

                 while (reader.Read())
                 {
                     Cliente c = new Cliente();
                     c.IDCliente = reader.GetInt32(0);
                     c.Nome = reader.GetString(1);
                     c.CNPJ = reader.GetString(2);
                     c.Cidade = reader.GetString(3);
                     c.Estado = reader.GetString(4);
                     c.Ativo = reader.GetBoolean(5);
                     cliente.Add(c);
                 }
             }
             finally
             {
                 if (reader != null)
                 {
                     reader.Close();
                 }
                 if (conn != null)
                 {
                     conn.Close();
                 }
             }
             return cliente;
         }               
    }
}
            
        
   
